package model.matricula;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.aula.Aula;
import model.banco.ConexaoFactory;
import model.esporte.Esporte;

public class MatriculaDAO {

	public void matricular(int id_socio, int id_aula) {
		Connection con = ConexaoFactory.getInstance().getConexao();

		String sql = "insert into clube.matricula (id_socio, id_aula) values (?, ?)";
		PreparedStatement statement = null;
		try {
			statement = con.prepareStatement(sql);
			statement.setInt(1, id_socio);
			statement.setInt(2, id_aula);
			statement.execute();
			statement.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Aula> pesquisar(int id_socio) {
		List<Aula> aulas = new ArrayList<Aula>();
		Connection con = ConexaoFactory.getInstance().getConexao();

		String sql = "select socio.id, esporte.id id_esporte, aula.id id_aula, matricula.id, esporte.descricao descricao_esporte, aula.descricao descricao_aula, aula.valor valor " +
				"from clube.esporte esporte, clube.aula aula, clube.pessoa socio, clube.matricula matricula " +
				"where" +
				" aula.id_esporte = esporte.id and " +
				"matricula.id_socio = socio.id and " +
				"matricula.id_aula = aula.id and " +
				"socio.id = id_socio";
		
		PreparedStatement statement = null;
		ResultSet result = null;
		
		
		try {
			statement = ConexaoFactory.getInstance().getConexao().prepareStatement(sql);
			result = statement.executeQuery();
			while (result.next()){
				Aula aula = new Aula();
				Esporte esporte = new Esporte();
				esporte.setId(result.getLong("id_esporte"));
				esporte.setDescricao(result.getString("descricao_esporte"));
				aula.setEsporte(esporte);
				aula.setId(result.getLong("id_aula"));
				aula.setDescricao(result.getString("descricao_aula"));
				aula.setValor(result.getFloat("valor"));
				aulas.add(aula);
			}
			result.close();
			statement.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return aulas;
	}
}
